﻿using System;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using MyLib;
using System.Windows.Forms;
using System.IO;
using System.Collections;

public class MyExcel : Object
{
    //excel.Cells.Font.Size = 12;
    //excel.Cells.Font.Bold = false;
    //sheet.get_Range(excel.Cells[1, 3], excel.Cells[1, 3]).Font.Size = 24;
    //sheet.get_Range(excel.Cells[1, 3], excel.Cells[1, 3]).Font.Bold = true;
    //sheet.get_Range(excel.Cells[3, 1], excel.Cells[3, 1]).Font.ColorIndex = 3; //red

    public Excel.Application excel = null;
    public Excel.Workbook book = null;
    public Excel.Worksheet sheet = null;
    public int max_rows = 0;
    public int max_columns = 0;
    public string filename = "";
    bool openned = false;
    IntPtr hwnd;
    int pid = 0;
    bool new_file = false;

    #region Win32API
    public struct MSG
    {
        public IntPtr handle;
        public uint msg;
        public IntPtr wParam;
        public IntPtr lParam;
        public uint time;
        public System.Drawing.Point p;
    }

    [DllImport("user32.dll", SetLastError = true)]
    private static extern bool PeekMessage(out MSG lpMsg, IntPtr hWnd, uint wMsgFilterMin, uint wMsgFilterMax, uint wRemoveMsg);
    [DllImport("user32.dll", SetLastError = true)]
    static extern bool TranslateMessage(ref MSG lpMsg);
    [DllImport("user32.dll", SetLastError = true)]
    static extern bool DispatchMessage(ref MSG lpMsg);

    [DllImport("User32.dll", CharSet = CharSet.Auto)]
    public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

    [DllImport("kernel32.dll")]
    public static extern int OpenProcess(int dwDesiredAccess, bool bInheritHandle, int dwProcessId);

    public const int PM_REMOVE = 0x0001;
    public const int PROCESS_ALL_ACCESS = 0x1F0FFF;
    public const int PROCESS_VM_READ = 0x0010;
    public const int PROCESS_VM_WRITE = 0x0020;
    #endregion

    public MyExcel()
    {
    }

    public MyExcel(string filename)
    {
        Open(filename);
    }

    public MyExcel(string filename, string sheet)
    {
        Open(filename, sheet);
    }

    ~MyExcel()
    {
        if (openned)
            Close();
    }

    object Missing
    {
        get
        {
            return System.Reflection.Missing.Value;
        }
    }

    public bool Create()
    {
        try
        {
            //object missing = System.Reflection.Missing.Value;
            excel = new Excel.Application();
            book = excel.Application.Workbooks.Add(true);
            hwnd = new IntPtr(excel.Hwnd);
            GetWindowThreadProcessId(hwnd, out pid);
            //sheet = (Excel.Worksheet)book.Worksheets[1];
            sheet = (Excel.Worksheet)book.ActiveSheet;
            max_rows = sheet.UsedRange.Cells.Row + sheet.UsedRange.Cells.Rows.Count;
            max_columns = sheet.UsedRange.Cells.Column + sheet.UsedRange.Cells.Columns.Count;

            excel.Visible = false;
            excel.DisplayAlerts = false;
            excel.AlertBeforeOverwriting = false;

            openned = true;
            return true;
        }
        catch (Exception ex)
        {
            Close();
            throw ex;
        }
    }

    public bool Open(string filename)
    {
        return Open(filename, "", false, false);
    }

    public bool Open(string filename, string sheet)
    {
        return Open(filename, sheet, false, false);
    }

    public bool Open(string filename, string sheet_name, bool readyonly, bool create)
    {
        try
        {
            object missing = System.Reflection.Missing.Value;
            excel = new Excel.Application();
            new_file = create;
            if (create)
                book = excel.Application.Workbooks.Add(missing);
            else
                book = excel.Application.Workbooks.Open(filename, missing, readyonly, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            hwnd = new IntPtr(excel.Hwnd);
            GetWindowThreadProcessId(hwnd, out pid);
            //sheet = (Excel.Worksheet)book.Worksheets[1];
            if (sheet_name == "")
                sheet = (Excel.Worksheet)book.ActiveSheet;
            else
            {
                try
                {
                    sheet = (Excel.Worksheet)book.Sheets[sheet_name];
                }
                catch (Exception ex)
                {
                    throw new Exception(string.Format("Open excel sheet '{0}' failed!", sheet_name));
                }
            }
            max_rows = sheet.UsedRange.Cells.Row + sheet.UsedRange.Cells.Rows.Count;
            max_columns = sheet.UsedRange.Cells.Column + sheet.UsedRange.Cells.Columns.Count;

            excel.Visible = false;
            excel.DisplayAlerts = false;
            excel.AlertBeforeOverwriting = false;

            this.filename = filename;
            this.openned = true;
            return true;
        }
        catch (Exception ex)
        {
            Close();
            throw ex;
        }
    }

    public bool Save()
    {
        try
        {
            if (this.filename != "")
            {
                if (new_file)
                    excel.ActiveWorkbook.SaveCopyAs(filename); //excel.ActiveWorkbook.SaveAs(filename, 56);
                else
                    excel.ActiveWorkbook.Save();
            }
            return true;
        }
        finally
        {
            Close();
        }
    }

    public bool SaveAs(string filename)
    {
        try
        {
            excel.ActiveWorkbook.SaveCopyAs(filename);
            return true;
        }
        finally
        {
            Close();
        }
    }

    public bool Close()
    {
        if (!openned)
            return true;

        try
        {
            if (book != null)
                book.Close(false, Type.Missing, Type.Missing);
            if (excel != null)
                excel.Quit();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

            // 10.调用GC的垃圾收集方法   
            GC.Collect();
            GC.WaitForPendingFinalizers();

        }
        catch (Exception)
        {
        }
        finally
        {
            try
            {
                if (excel != null && pid > 0)
                {
                    int ExcelProcess;
                    ExcelProcess = OpenProcess(PROCESS_VM_READ | PROCESS_VM_WRITE, false, pid);
                    if (ExcelProcess > 0)
                    {
                        try
                        {
                            System.Diagnostics.Process process = System.Diagnostics.Process.GetProcessById(pid);
                            process.Kill();
                        }
                        catch (Exception)
                        {
                        }
                    }
                }
            }
            catch (Exception)
            {
            }
            GC.Collect();
        }
        return true;
    }

    public void ProcessMessage()
    {
        //MSG msg;
        //while (PeekMessage(out msg, IntPtr.Zero, 0, 0, PM_REMOVE))
        //{
        //    TranslateMessage(ref msg);
        //    DispatchMessage(ref msg);
        //}
    }

    public void WriteString(int row, int col, string data)
    {
        sheet.Cells[row, col] = data;
    }

    public string ReadString(int row, int col)
    {
        //ProcessMessage();
        //object data = ((Excel.Range)excel.Cells[row, col]).Value;
        //object data = sheet.get_Range(sheet.Cells[row, col], sheet.Cells[row, col]).Value;
        object data = ((Excel.Range)sheet.Cells[row, col]).Value;
        if (data is string)
            return (string)data;
        else if (data is double)
        {
            Int64 n = Convert.ToInt64(data);
            if (n == (double)data)
                return n.ToString();
            else
                return data.ToString();
        }
        else if (data is DateTime)
        {
            DateTime dt = (DateTime)data;

            if (dt.Hour == 0 && dt.Minute == 0 && dt.Second == 0 && dt.Millisecond == 0)
                return dt.ToString("yyyy-MM-dd");
            else
                return dt.ToString("yyyy-MM-dd HH:mm:ss");
        }
        else if (data == null)        //增加判断  看是否是合并单元格
        {
            var cell = ((Excel.Range)excel.Cells[row, col]);
            if ((bool)cell.MergeCells == true)
            {
                // 本单元格是 “合并单元格”
                if (cell.MergeArea.Row == row
                    && cell.MergeArea.Column == col)
                {
                    // 当前单元格 就是 合并单元格的 左上角 内容.
                    data = cell.Value;
                    if (data is string)
                        return (string)data;
                    else if (data is double)
                    {
                        Int64 n = Convert.ToInt64(data);
                        if (n == (double)data)
                            return n.ToString();
                        else
                            return data.ToString();
                    }
                }
                else
                {
                    // 返回 合并单元格的 左上角 内容.
                    data = ((Excel.Range)excel.Cells[cell.MergeArea.Row, cell.MergeArea.Column]).Value;
                    if (data is string)
                        return (string)data;
                    else if (data is double)
                    {
                        Int64 n = Convert.ToInt64(data);
                        if (n == (double)data)
                            return n.ToString();
                        else
                            return data.ToString();
                    }

                }
            }
            else //不是合并单元格而且没内容
            {
                //throw new Exception("");
            }
            return "";       
        }
        else
            return data.ToString();
    }

    public int ReadInt(int row, int col)
    {
        try
        {
            object data = ((Excel.Range)excel.Cells[row, col]).Value;
            if (data is string)
                return int.Parse((string)data);
            else if (data is double)
                return Convert.ToInt32(data);
            else
                throw new Exception("");
        }
        catch
        {
            throw new Exception(String.Format("invalid int at [{0}, {1}] ", row, col));
        }
    }

    public decimal ReadDecimal(int row, int col)
    {
        try
        {
            object data = ((Excel.Range)excel.Cells[row, col]).Value;
            if (data is string)
                return decimal.Parse((string)data);
            else if (data is double)
                return Convert.ToDecimal(data);
            else if (data == null)      //没读取到内容
            {
                var cell = ((Excel.Range)excel.Cells[row, col]);
                if ((bool)cell.MergeCells == true)
                {
                    // 本单元格是 “合并单元格”
                    if (cell.MergeArea.Row == row
                        && cell.MergeArea.Column == col)
                    {
                        // 当前单元格 就是 合并单元格的 左上角 内容.
                        data = cell.Value;
                        if (data is string)
                            return decimal.Parse((string)data);
                        else if (data is double)
                            return Convert.ToDecimal(data);
                    }
                    else
                    {
                        // 返回 合并单元格的 左上角 内容.
                        data = ((Excel.Range)excel.Cells[cell.MergeArea.Row, cell.MergeArea.Column]).Value;
                        if (data is string)
                            return decimal.Parse((string)data);
                        else if (data is double)
                            return Convert.ToDecimal(data);
                    }
                }
                else     //不是合并单元格而且没内容
                {
                    throw new Exception("");
                }
                return 0;
            }
            else
                throw new Exception("");
        }
        catch
        {
            throw new Exception(String.Format("invalid decimal at [{0}, {1}] ", row, col));
        }
    }

    public DateTime ReadDateTime(int row, int col)
    {
        try
        {
            object data = ((Excel.Range)excel.Cells[row, col]).Value;
            if (data is DateTime)
                return (DateTime)data;
            else
                throw new Exception("");
        }
        catch
        {
            throw new Exception(String.Format("invalid DateTime at [{0}, {1}] ", row, col));
        }
    }

    public enum DateTimeFormat
    {
        yyyy_MM_dd,
        yyyy_MM_dd_HH_mm_ss,
        dd_MM_yyyy,
        ddth_MMM_yyyy,
        MM_dd_yyyy,
        MMM_SPACE_ddth_yyyy
    }

    public DateTime ReadDateTime(int row, int col, DateTimeFormat format)
    {
        try
        {
            object data = ((Excel.Range)excel.Cells[row, col]).Value;
            if (data is string)
            {
                switch (format)
                {
                    case DateTimeFormat.yyyy_MM_dd:
                        return ReadDateTime_yyyy_MM_dd((string)data);
                    case DateTimeFormat.yyyy_MM_dd_HH_mm_ss:
                        return ReadDateTime_yyyy_MM_dd_HH_mm_ss((string)data);
                    case DateTimeFormat.dd_MM_yyyy:
                        return ReadDateTime_dd_MM_yyyy((string)data);
                    case DateTimeFormat.ddth_MMM_yyyy:
                        return ReadDateTime_ddth_MMM_yyyy((string)data);
                    case DateTimeFormat.MM_dd_yyyy:
                        return ReadDateTime_MM_dd_yyyy((string)data);
                    case DateTimeFormat.MMM_SPACE_ddth_yyyy:
                        return ReadDateTime_MMM_SPACE_ddth_yyyy((string)data);
                    default:
                        throw new Exception("invalid format");
                }
            }
            else if (data is DateTime)
                return (DateTime)data;
            else
                throw new Exception("");
        }
        catch
        {
            throw new Exception(String.Format("invalid DateTime at [{0}, {1}] ", row, col));
        }
    }

    DateTime ReadDateTime_dd_MM_yyyy(string s)
    {
        string[] ss = s.Split(new char[] { ' ', '-', '/', '\\', '.' });
        if (ss.Length != 3)
            throw new Exception("invalid format");
        if (ss[2].Length == 2)        //如果是12，13这种类型   前面加20
            ss[2] = "20" + ss[2];
        return new DateTime(Convert.ToInt32(ss[2]), Convert.ToInt32(ss[1]), Convert.ToInt32(ss[0]));
    }

    DateTime ReadDateTime_yyyy_MM_dd(string s)
    {
        string[] ss = s.Split(new char[] { ' ', '-', '/', '\\' });
        if (ss.Length != 3)
            throw new Exception("invalid format");
        return new DateTime(Convert.ToInt32(ss[0]), Convert.ToInt32(ss[1]), Convert.ToInt32(ss[2]));
    }

    DateTime ReadDateTime_yyyy_MM_dd_HH_mm_ss(string s)
    {
        string[] ss = s.Split(new char[] { ' ', '-', '/', '\\', ':' });
        if (ss.Length != 6)
            throw new Exception("invalid format");
        return new DateTime(Convert.ToInt32(ss[0]), Convert.ToInt32(ss[1]), Convert.ToInt32(ss[2]), Convert.ToInt32(ss[3]), Convert.ToInt32(ss[4]), Convert.ToInt32(ss[5]));
    }

    DateTime ReadDateTime_ddth_MMM_yyyy(string s)
    {
        string[] ss = s.Split(new char[] { ' ', '-', '/', '\\' });
        if (ss.Length != 3)
            throw new Exception("invalid format");
        return new DateTime(Convert.ToInt32(ss[2]), MMMtoMM(ss[1]), Convert.ToInt32(ss[0].Replace("st", "").Replace("nd", "").Replace("th", "")));
    }

    DateTime ReadDateTime_MMM_SPACE_ddth_yyyy(string s)
    {
        string[] ss = s.Split(new char[] { ',' });
        if (ss.Length != 2)
            throw new Exception("invalid format");
        string[] ss1 = ss[0].Split(new char[] { ' ' });
        if (ss1.Length != 2)
            throw new Exception("invalid format");

        return new DateTime(Convert.ToInt32(ss[1]), MMMtoMM(ss1[0]), Convert.ToInt32(ss1[1].Replace("st", "").Replace("nd", "").Replace("th", "")));
    }






    DateTime ReadDateTime_MM_dd_yyyy(string s)
    {
        string[] ss = s.Split(new char[] { ' ', '-', '/', '\\' });
        //if (ss.Length != 3)
        //    throw new Exception("invalid format");
        return new DateTime(Convert.ToInt32(ss[2]), Convert.ToInt32(ss[0]), Convert.ToInt32(ss[1]));
    }

    int MMMtoMM(string MMM)
    {
        switch (MMM.ToLower())
        {
            case "jan":
            case "january": return 1;
            case "feb":
            case "february": return 2;
            case "mar":
            case "march": return 3;
            case "apr":
            case "april": return 4;
            case "may": return 5;
            case "jun":
            case "june": return 6;
            case "jul":
            case "july": return 7;
            case "aug":
            case "august": return 8;
            case "sep":
            case "september": return 9;
            case "oct":
            case "october": return 10;
            case "nov":
            case "november": return 11;
            case "dec":
            case "december": return 12;
            default:
                throw new Exception("Invalid month! '" + MMM + "'");
        }
    }

    public TimeSpan ReadTime(int row, int col)
    {
        try
        {
            object data = ((Excel.Range)excel.Cells[row, col]).Value;
            if (data is string)
            {
                string[] ss = ((string)data).Split(new char[] { ' ', '-', '/', '\\', ':' });
                if (ss.Length != 3)
                    throw new Exception("invalid format");
                return new TimeSpan(Convert.ToInt32(ss[0]), Convert.ToInt32(ss[1]), Convert.ToInt32(ss[2]));
            }
            else
                throw new Exception("");
        }
        catch
        {
            throw new Exception(String.Format("invalid DateTime at [{0}, {1}] ", row, col));
        }
    }

    public void CopyInsert(int from, int to)
    {
        Excel.Range row = (Excel.Range)excel.Rows[from + 1, Missing];
        row.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, Missing);

        excel.Rows[String.Format("{0}:{0}", from, from)].Select();
        excel.Range[String.Format("A{0}", from)].Activate();
        excel.Selection.Copy();
        excel.Rows[String.Format("{0}:{0}", to, to)].Select();
        //excel.Rows.Insert();
        excel.ActiveSheet.Paste();
        SelectNone();
    }

    public void SelectNone()
    {
        excel.Cells[1, 1].Select();
    }

    #region Style Operation
    /// <summary>
    /// 单元格背景色及填充方式
    /// </summary>
    /// <param name="startRow">起始行</param>
    /// <param name="startColumn">起始列</param>
    /// <param name="endRow">结束行</param>
    /// <param name="endColumn">结束列</param>
    /// <param name="color">颜色索引</param>
    public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
    {
        Excel.Range range = excel.Range[excel.Cells[startRow, startColumn], excel.Cells[endRow, endColumn]];
        range.Interior.ColorIndex = color;
        range.Interior.Pattern = Pattern.Solid;
    }

    /// <summary>
    /// 单元格背景色及填充方式
    /// </summary>
    /// <param name="startRow">起始行</param>
    /// <param name="startColumn">起始列</param>
    /// <param name="endRow">结束行</param>
    /// <param name="endColumn">结束列</param>
    /// <param name="color">颜色索引</param>
    /// <param name="pattern">填充方式</param>
    public void CellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color, Pattern pattern)
    {
        Excel.Range range = excel.get_Range(excel.Cells[startRow, startColumn], excel.Cells[endRow, endColumn]);
        range.Interior.ColorIndex = color;
        range.Interior.Pattern = pattern;
    }

    /// <summary>
    /// 设置行高
    /// </summary>
    /// <param name="startRow">起始行</param>
    /// <param name="endRow">结束行</param>
    /// <param name="height">行高</param>
    public void SetRowHeight(int startRow, int endRow, int height)
    {
        //获取当前正在使用的工作表
        Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
        Excel.Range range = (Excel.Range)worksheet.Rows[startRow.ToString() + ":" + endRow.ToString(), System.Type.Missing];
        range.RowHeight = height;
    }

    /// <summary>
    /// 自动调整行高
    /// </summary>
    /// <param name="columnNum">列号</param>
    public void RowAutoFit(int rowNum)
    {
        //获取当前正在使用的工作表
        Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
        Excel.Range range = (Excel.Range)worksheet.Rows[rowNum.ToString() + ":" + rowNum.ToString(), System.Type.Missing];
        range.EntireColumn.AutoFit();

    }

    /// <summary>
    /// 设置列宽
    /// </summary>
    /// <param name="startColumn">起始列(列对应的字母)</param>
    /// <param name="endColumn">结束列(列对应的字母)</param>
    /// <param name="width"></param>
    public void SetColumnWidth(string startColumn, string endColumn, int width)
    {
        //获取当前正在使用的工作表
        Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
        Excel.Range range = (Excel.Range)worksheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
        range.ColumnWidth = width;
    }

    /// <summary>
    /// 设置列宽
    /// </summary>
    /// <param name="startColumn">起始列</param>
    /// <param name="endColumn">结束列</param>
    /// <param name="width"></param>
    public void SetColumnWidth(int startColumn, int endColumn, int width)
    {
        string strStartColumn = GetColumnName(startColumn);
        string strEndColumn = GetColumnName(endColumn);
        //获取当前正在使用的工作表
        Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
        Excel.Range range = (Excel.Range)worksheet.Columns[strStartColumn + ":" + strEndColumn, System.Type.Missing];
        range.ColumnWidth = width;
    }

    /// <summary>
    /// 自动调整列宽
    /// </summary>
    /// <param name="columnNum">列号</param>
    public void ColumnAutoFit(string column)
    {
        //获取当前正在使用的工作表
        Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
        Excel.Range range = (Excel.Range)worksheet.Columns[column + ":" + column, System.Type.Missing];
        range.EntireColumn.AutoFit();

    }

    /// <summary>
    /// 自动调整列宽
    /// </summary>
    /// <param name="columnNum">列号</param>
    public void ColumnAutoFit(int columnNum)
    {
        string strcolumnNum = GetColumnName(columnNum);
        //获取当前正在使用的工作表
        Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
        Excel.Range range = (Excel.Range)worksheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
        range.EntireColumn.AutoFit();

    }

    /// <summary>
    /// 字体颜色
    /// </summary>
    /// <param name="startRow">起始行</param>
    /// <param name="startColumn">起始列</param>
    /// <param name="endRow">结束行</param>
    /// <param name="endColumn">结束列</param>
    /// <param name="color">颜色索引</param>
    public void FontColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color)
    {
        Excel.Range range = excel.get_Range(excel.Cells[startRow, startColumn], excel.Cells[endRow, endColumn]);
        range.Font.ColorIndex = color;
    }

    /// <summary>
    /// 字体样式(加粗,斜体,下划线)
    /// </summary>
    /// <param name="startRow">起始行</param>
    /// <param name="startColumn">起始列</param>
    /// <param name="endRow">结束行</param>
    /// <param name="endColumn">结束列</param>
    /// <param name="isBold">是否加粗</param>
    /// <param name="isItalic">是否斜体</param>
    /// <param name="underline">下划线类型</param>
    public void FontStyle(int startRow, int startColumn, int endRow, int endColumn, bool isBold, bool isItalic, UnderlineStyle underline)
    {
        Excel.Range range = excel.get_Range(excel.Cells[startRow, startColumn], excel.Cells[endRow, endColumn]);
        range.Font.Bold = isBold;
        range.Font.Underline = underline;
        range.Font.Italic = isItalic;
    }

    /// <summary>
    /// 单元格字体及大小
    /// </summary>
    /// <param name="startRow">起始行</param>
    /// <param name="startColumn">起始列</param>
    /// <param name="endRow">结束行</param>
    /// <param name="endColumn">结束列</param>
    /// <param name="fontName">字体名称</param>
    /// <param name="fontSize">字体大小</param>
    public void FontNameSize(int startRow, int startColumn, int endRow, int endColumn, string fontName, int fontSize)
    {
        Excel.Range range = excel.get_Range(excel.Cells[startRow, startColumn], excel.Cells[endRow, endColumn]);
        range.Font.Name = fontName;
        range.Font.Size = fontSize;
    }
    #endregion

    #region 私有成员
    private string GetColumnName(int number)
    {
        int h, l;
        h = number / 26;
        l = number % 26;
        if (l == 0)
        {
            h -= 1;
            l = 26;
        }
        string s = GetLetter(h) + GetLetter(l);
        return s;
    }

    private string GetLetter(int number)
    {
        switch (number)
        {
            case 1:
                return "A";
            case 2:
                return "B";
            case 3:
                return "C";
            case 4:
                return "D";
            case 5:
                return "E";
            case 6:
                return "F";
            case 7:
                return "G";
            case 8:
                return "H";
            case 9:
                return "I";
            case 10:
                return "J";
            case 11:
                return "K";
            case 12:
                return "L";
            case 13:
                return "M";
            case 14:
                return "N";
            case 15:
                return "O";
            case 16:
                return "P";
            case 17:
                return "Q";
            case 18:
                return "R";
            case 19:
                return "S";
            case 20:
                return "T";
            case 21:
                return "U";
            case 22:
                return "V";
            case 23:
                return "W";
            case 24:
                return "X";
            case 25:
                return "Y";
            case 26:
                return "Z";
            default:
                return "";
        }
    }
    #endregion

    /// <summary>
    /// 水平对齐方式
    /// </summary>
    public enum ExcelHAlign
    {
        常规 = 1,
        靠左,
        居中,
        靠右,
        填充,
        两端对齐,
        跨列居中,
        分散对齐
    }

    /// <summary>
    /// 垂直对齐方式
    /// </summary>
    public enum ExcelVAlign
    {
        靠上 = 1,
        居中,
        靠下,
        两端对齐,
        分散对齐
    }

    /// <summary>
    /// 线粗
    /// </summary>
    public enum BorderWeight
    {
        极细 = 1,
        细 = 2,
        粗 = -4138,
        极粗 = 4
    }

    /// <summary>
    /// 线样式
    /// </summary>
    public enum LineStyle
    {
        连续直线 = 1,
        短线 = -4115,
        线点相间 = 4,
        短线间两点 = 5,
        点 = -4118,
        双线 = -4119,
        无 = -4142,
        少量倾斜点 = 13
    }

    /// <summary>
    /// 下划线方式
    /// </summary>
    public enum UnderlineStyle
    {
        无下划线 = -4142,
        双线 = -4119,
        双线充满全格 = 5,
        单线 = 2,
        单线充满全格 = 4
    }

    /// <summary>
    /// 单元格填充方式
    /// </summary>
    public enum Pattern
    {
        Automatic = -4105,
        Checker = 9,
        CrissCross = 16,
        Down = -4121,
        Gray16 = 17,
        Gray25 = -4124,
        Gray50 = -4125,
        Gray75 = -4126,
        Gray8 = 18,
        Grid = 15,
        Horizontal = -4128,
        LightDown = 13,
        LightHorizontal = 11,
        LightUp = 14,
        LightVertical = 12,
        None = -4142,
        SemiGray75 = 10,
        Solid = 1,
        Up = -4162,
        Vertical = -4166
    }

    /// <summary>
    /// 常用颜色定义,对就Excel中颜色名
    /// </summary>
    public enum ColorIndex
    {
        黑色 = 1,
        白色 = 2,
        红色 = 3,
        鲜绿 = 4,
        蓝色 = 5,
        黄色 = 6,
        粉红 = 7,
        青绿 = 8,
        深红 = 9,
        绿色 = 10,
        深蓝 = 11,
        深黄 = 12,
        紫罗兰 = 13,
        青色 = 14,
        灰色25 = 15,
        灰色50 = 16,
        天蓝 = 33,
        浅青绿 = 34,
        浅绿 = 35,
        浅黄 = 36,
        淡蓝 = 37,
        玫瑰红 = 38,
        淡紫 = 39,
        茶色 = 40,
        浅蓝 = 41,
        水绿色 = 42,
        酸橙色 = 43,
        金色 = 44,
        浅橙色 = 45,
        橙色 = 46,
        蓝灰 = 47,
        灰色40 = 48,
        深青 = 49,
        海绿 = 50,
        深绿 = 51,
        橄榄 = 52,
        褐色 = 53,
        梅红 = 54,
        靛蓝 = 55,
        灰色80 = 56,
        无色 = -4142,
        自动 = -4105
    }

}

public class MyExcelUtility
{
    class TemplateDataRowDefination
    {
        public string name;
        public int row;
        public int col;
        public bool is_datarow;
        public int src_data_col;

        public TemplateDataRowDefination(string name, int row, int col, bool is_datarow, int src_data_col)
        {
            this.name = name;
            this.row = row;
            this.col = col;
            this.is_datarow = is_datarow;
            this.src_data_col = src_data_col;
        }
    }

    static void GetTemplateDefination(MyExcel excel, DataGridView dgv, out int row, out int col, out List<TemplateDataRowDefination> defination)
    {
        row = 0;
        col = 0;
        defination = new List<TemplateDataRowDefination>();
        bool found = false;
        for (int i = 1; i < excel.max_rows; i++)
        {
            for (int j = 1; j < excel.max_columns; j++)
            {
                string s = excel.ReadString(i, j);
                if (s.LeftCompare("{{ROW||"))
                {
                    if (!found)
                    {
                        row = i;
                        col = j;
                        found = true;
                    }
                    defination.Add(new TemplateDataRowDefination(s.Replace("{{ROW||", "").Replace("}}", ""), i, j, true, -1));
                }
                else if (s.LeftCompare("{{"))
                    defination.Add(new TemplateDataRowDefination(s.Replace("{{", "").Replace("}}", ""), i, j, false, -1));
            }
        }
        if (!found)
            throw new Exception("Can not first any defination in template!");

        foreach (TemplateDataRowDefination def in defination)
        {
            if (def.is_datarow)
            {
                int p = -1;
                foreach (DataGridViewColumn column in dgv.Columns)
                    if (String.Compare(column.HeaderText, def.name, true) == 0)
                        p = column.Index;
                if (p == -1)
                    throw new Exception(String.Format("Template field '{0}' does not exist in DataGridView!", def.name));
                def.src_data_col = p;
            }
        }
    }

    static void ProcessPreDefinedData(MyExcel excel, List<TemplateDataRowDefination> defination, Hashtable src)
    {
        foreach (object a in src.Keys)
        {
            bool found = false;
            string key = a.ToString();
            string data = src[key].ToString();
            for (int i = 0; i < defination.Count; i++)
            {
                if (defination[i].is_datarow)
                    continue;
                if (String.Compare(defination[i].name, key, true) == 0)
                {
                    excel.WriteString(defination[i].row, defination[i].col, data);
                    found = true;
                    break;
                }
            }
            if (!found)
                throw new Exception(String.Format("Can not find key '{0}' in template!", key));
        }
    }

    public delegate void OnProgress(int pos);

    static public void ExportDataGridView(DataGridView dgv, string filename, string sheet, OnProgress progress)
    {
        if (progress != null)
            progress(0);

        MyExcel excel = new MyExcel();
        try
        {
            excel.Open(filename, sheet, false, true);
            for (int j = 0; j < dgv.Columns.Count; j++)
                excel.WriteString(1, j + 1, dgv.Columns[j].HeaderText);
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                for (int j = 0; j < dgv.Columns.Count; j++)
                    excel.WriteString(i + 2, j + 1, dgv.Rows[i].Cells[j].Value.ToString());
                if (progress != null)
                    progress(Convert.ToInt32(i * 1.0 / dgv.Rows.Count));
            }
            excel.Save();
            if (progress != null)
                progress(100);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            excel.Close();
            excel = null;
            //OnProcess(100);
        }
    }

    static public void ExportDataGridView(string template, DataGridView dgv, string filename, Hashtable data, OnProgress progress)
    {
        if (progress != null)
            progress(0);

        File.Copy(template, filename, true);
        MyExcel excel = new MyExcel();
        try
        {
            excel.Open(filename);

            int first_row, first_col;
            List<TemplateDataRowDefination> template_def = new List<TemplateDataRowDefination>();
            GetTemplateDefination(excel, dgv, out first_row, out first_col, out template_def);

            ProcessPreDefinedData(excel, template_def, data);

            //clear defination line
            foreach (TemplateDataRowDefination def in template_def)
                if (def.is_datarow)
                    excel.WriteString(def.row, def.col, "");
            //copy line
            for (int i = 1; i < dgv.Rows.Count; i++)
            {
                excel.CopyInsert(first_row, first_row + i);
                if (progress != null)
                    progress(Convert.ToInt32(i * 50.0 / dgv.Rows.Count));
            }

            //OnProcess(0);
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                foreach (TemplateDataRowDefination def in template_def)
                    if (def.is_datarow)
                    {
                        excel.WriteString(first_row + i, def.col, dgv.Rows[i].Cells[def.src_data_col].Value.ToString());
                        //if (i % 2 == 0)
                        //    excel.CellsBackColor(first_row + i, def.col, first_row + i, def.col, MyExcel.ColorIndex.无色);
                        //else
                        //    excel.CellsBackColor(first_row + i, def.col, first_row + i, def.col, MyExcel.ColorIndex.灰色25);
                    }
                if (progress != null)
                    progress(Convert.ToInt32(50 + i * 50.0 / dgv.Rows.Count));
            }
            excel.Save();
            if (progress != null)
                progress(100);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            excel.Close();
            excel = null;
            //OnProcess(100);
        }
    }
}